import { Provide, Scope, ScopeEnum, Init } from '@midwayjs/core';
import * as sqlite3 from 'sqlite3';
import { promisify } from 'util';

/**
 * 数据库服务类
 * 负责SQLite数据库的初始化和操作
 */
@Provide()
@Scope(ScopeEnum.Singleton)
export class DatabaseService {
  private db: sqlite3.Database;

  /**
   * 初始化数据库连接和表结构
   */
  @Init()
  async init() {
    // 创建数据库连接
    this.db = new sqlite3.Database('./data/words.db');
    
    // 创建表结构
    await this.createTables();
  }

  /**
   * 创建数据库表
   */
  private async createTables(): Promise<void> {
    const runAsync = promisify(this.db.run.bind(this.db));
    
    // 创建tb_word表
    const createWordTableSQL = `
      CREATE TABLE IF NOT EXISTS tb_word (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        word TEXT UNIQUE NOT NULL,
        response TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;
    
    // 创建tb_cache表
    const createCacheTableSQL = `
      CREATE TABLE IF NOT EXISTS tb_cache (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        prompt TEXT UNIQUE NOT NULL,
        content TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;
    
    await runAsync(createWordTableSQL);
    await runAsync(createCacheTableSQL);
  }

  /**
   * 根据单词查询缓存的翻译结果
   * @param word 要查询的单词（会自动去除空格）
   * @returns 翻译结果或null
   */
  async getWordTranslation(word: string): Promise<string | null> {
    const cleanWord = word.trim().toLowerCase();
    const getAsync = promisify(this.db.get.bind(this.db));
    
    const result = await getAsync(
      'SELECT response FROM tb_word WHERE word = ?',
      [cleanWord]
    ) as { response: string } | undefined;
    
    return result ? result.response : null;
  }

  /**
   * 保存单词翻译结果到数据库
   * @param word 单词（会自动去除空格）
   * @param response 翻译结果
   */
  async saveWordTranslation(word: string, response: string): Promise<void> {
    const cleanWord = word.trim().toLowerCase();
    const runAsync = promisify(this.db.run.bind(this.db));
    
    await runAsync(
      `INSERT OR REPLACE INTO tb_word (word, response, updated_at) 
       VALUES (?, ?, CURRENT_TIMESTAMP)`,
      [cleanWord, response]
    );
  }

  /**
   * 根据prompt查询缓存的AI结果
   * @param prompt 用户输入的问题（会自动去除空格）
   * @returns AI结果或null
   */
  async getCacheByPrompt(prompt: string): Promise<string | null> {
    const cleanPrompt = prompt.trim();
    const getAsync = promisify(this.db.get.bind(this.db));
    
    const result = await getAsync(
      'SELECT content FROM tb_cache WHERE prompt = ?',
      [cleanPrompt]
    ) as { content: string } | undefined;
    
    return result ? result.content : null;
  }

  /**
   * 保存AI查询结果到缓存
   * @param prompt 用户问题（会自动去除空格）
   * @param content AI回答内容
   */
  async saveCacheResult(prompt: string, content: string): Promise<void> {
    const cleanPrompt = prompt.trim();
    const runAsync = promisify(this.db.run.bind(this.db));
    
    await runAsync(
      `INSERT OR REPLACE INTO tb_cache (prompt, content, updated_at) 
       VALUES (?, ?, CURRENT_TIMESTAMP)`,
      [cleanPrompt, content]
    );
  }

  /**
   * 获取数据库实例（用于其他复杂查询）
   */
  getDatabase(): sqlite3.Database {
    return this.db;
  }
}